IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id('MD3.RemoveUniqueConstraints'))
BEGIN
	DROP PROCEDURE MD3.RemoveUniqueConstraints
END
GO

CREATE PROCEDURE MD3.RemoveUniqueConstraints
	--SchemaName, Tablename, and KeyColumns for filter of keys to be removed from referenced table.
	@SchemaName sysname --Referenced table schema
	,@TableName sysname --Referenced table 
	,@KeyColumns varchar(500)  --Columns referenced by FK

	AS

BEGIN  
	
	DECLARE @SQL VARCHAR(MAX) = '' ,@ConstraintName SYSNAME
	DECLARE @Columns TABLE (ColumnName SYSNAME, Sequence SMALLINT) 
	DECLARE @UniqueConstrCols TABLE (ConstraintName SYSNAME NOT NULL, ColList varchar(max) NOT NULL)   
    
    -- build the list of unique constraints on the table with csv column list
    INSERT INTO @UniqueConstrCols (ConstraintName, ColList) 
    SELECT k.name,
            (SELECT 
                CASE 
                    WHEN ic.key_ordinal = 1 THEN c.name
                    ELSE ','+ c.name
                END
             FROM sys.index_columns as ic
             JOIN sys.columns as c
             ON c.object_id = t.object_id
             AND c.column_id = ic.column_id
             WHERE ic.object_id = t.object_id
             AND ic.index_id = k.unique_index_id
             ORDER BY ic.key_ordinal
             FOR XML PATH('')
            ) 
    FROM sys.key_constraints as k
    JOIN sys.tables as t
    ON t.object_id = k.parent_object_id
    WHERE k.type_desc = 'UNIQUE_CONSTRAINT'
    AND t.object_id = OBJECT_ID(@SchemaName +'.'+ @TableName)

    SELECT @ConstraintName = UCC.ConstraintName
    FROM @UniqueConstrCols UCC 
    WHERE UCC.ColList = @KeyColumns

    IF @ConstraintName IS NOT NULL 
    BEGIN

	    SELECT @SQL = ' ALTER TABLE ' + @SchemaName + '.' + @TableName + ' DROP CONSTRAINT ' + @ConstraintName 
		EXEC MD3.ExecuteSQL @SQL;

    END

END;
GO
